package pers.cz.excel;

import cn.hutool.core.date.DateUtil;
import com.alibaba.fastjson.JSONObject;
import com.google.gson.Gson;
import entity.DataBaseParamter;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import pers.cz.model.AutoTestCaseTemplate;
import pers.cz.model.TestCase;
import pers.cz.obj.ObjectUtils;
import org.apache.poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.util.*;

/**
 * 工具类，用于文件上传、下载、解析
 * @program: PostGirl-panent
 * @description: PoiUtils
 * @author: Cheng Zhi
 * @create: 2021-02-28 09:57
 **/
public class PoiUtils {

    private static Logger logger = LoggerFactory.getLogger(PoiUtils.class);

    /**
     * 将对象集合导出到excel
     * @param list
     * @param <T>
     * @return
     */
    public static <T> ResponseEntity<byte[]> exportToExcel(List<T> list) {

        // 1、创建一个excel文档
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 2、创建文档摘要
        workbook.createInformationProperties();
        // 3、获取并配置文档摘要信息
        DocumentSummaryInformation docInfo = workbook.getDocumentSummaryInformation();
        // 文档类别
        docInfo.setCategory("文档类别");
        // 文档管理员
        docInfo.setManager("PostGirl");
        // 设置公司信息
        docInfo.setCompany("www.postgirl.com");
        // 4、获取文档摘要信息
        SummaryInformation summaryInformation = workbook.getSummaryInformation();
        // 文档标题
        summaryInformation.setTitle("文档标题");
        // 文档作者
        summaryInformation.setAuthor("PostGirl");
        // 备注信息
        summaryInformation.setComments("本文档由 PostGirl 提供");

        // 5、创建样式
        // 创建标题行的样式
        HSSFCellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex()); // 背景颜色
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 填充模式

        HSSFSheet sheet = workbook.createSheet();// 不传name 默认为sheet1
        // 6、创建标题行 第一行数据
        // 只循环一次目的是将对象名写入到excel标题上
        for (T t : list) {
            HSSFRow row = sheet.createRow(0);
            String[] fieldNames = ObjectUtils.getFiledNames(t);
            for (int i=0; i<fieldNames.length; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellValue(fieldNames[i]);
                cell.setCellStyle(headerStyle);
            }
            break;
        }

        // 7、创建后面行
        for (int j=0; j<list.size(); j++) {
            T t = list.get(j);
            String[] fieldValues = ObjectUtils.getFieldValues(t);
            // 由于第一行已经写入了标题，所以这里从第二行开始写
            HSSFRow rows = sheet.createRow(j+1);
            for (int i=0; i<fieldValues.length; i++) {
                rows.createCell(i).setCellValue(fieldValues[i]);
            }
        }
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        HttpHeaders headers = new HttpHeaders();
        try {
            // 防止乱码
            headers.setContentDispositionFormData("attachment",new String("系统导出文件.xls".getBytes("UTF-8"),"ISO-8859-1"));
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            workbook.write(baos);

        } catch (IOException e) {
            logger.error("导出文件失败！", e);
        }

        return new ResponseEntity<byte[]>(baos.toByteArray(),headers, HttpStatus.CREATED);
    }

    /**
     * 传入文件，解析并返回实体集合，用于后续操作
     * @param file
     * @return
     */
    public static List<AutoTestCaseTemplate> excelToTestCaseTemplate (MultipartFile file, Integer userId) throws IOException {
        List<AutoTestCaseTemplate> list = new ArrayList<>();
        AutoTestCaseTemplate testCase = null;

        try {
            // 1、创建一个workbook对象
            HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
            // 2、获取workboot中表单的数量
            int numberOfSheets = workbook.getNumberOfSheets();
            for (int i=0; i<numberOfSheets; i++) {
                // 3、获取sheet页
                HSSFSheet sheet = workbook.getSheetAt(i);
                // 4、获取sheet页中的行数
                int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();

                for (int j=0; j<physicalNumberOfRows; j++) {
                    if (j==0) {
                        continue; // 跳过标题行
                    }
                    // 6、获取行
                    HSSFRow row = sheet.getRow(j);
                    if (row == null) {
                        continue; // 防止数据中间有空行
                    }
                    // 7、获取列数
                    //int physicalNumberOfCells = row.getPhysicalNumberOfCells(); // 由于poi获取到如果是空白列不会认为是一列，所以会获取到的列数不准确，这里通过反射后去属性的个数作为总列数
                    Class<AutoTestCaseTemplate> clazz = AutoTestCaseTemplate.class;
                    int physicalNumberOfCells = clazz.getDeclaredFields().length;
                    testCase = new AutoTestCaseTemplate();


                    // 案例最后修改日期
                    testCase.setCreateTime(DateUtil.date());
                    // 案例创建时间
                    for (int k=0; k<=physicalNumberOfCells; k++) {
                        HSSFCell cell = row.getCell(k);
                        switch (k) {
                            case 1:
                                //设置单元格类型
                                cell.setCellType(CellType.STRING);
                                testCase.setTemplateName(cell.getStringCellValue());
                                break;
                            case 2:
                                //设置单元格类型
                                cell.setCellType(CellType.STRING);
                                testCase.setTemplate(cell.getStringCellValue());
                                break;
                            default:
                                break;
                        }
                    }
                    list.add(testCase);
                }
            }
        } catch (Exception e) {
            logger.error("解析文件失败！", e);
            throw new IOException();
        }

        return list;
    }

    /**
     * 传入文件，解析并返回实体集合，用于后续操作
     * @param file
     * @return
     */
    public static List<TestCase> excelToEntity (MultipartFile file, Integer userId) throws IOException {
        List<TestCase> list = new ArrayList<>();
        TestCase testCase = null;
        try {
            // 1、创建一个workbook对象
            HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
            // 2、获取workboot中表单的数量
            int numberOfSheets = workbook.getNumberOfSheets();
            for (int i=0; i<numberOfSheets; i++) {
                // 3、获取sheet页
                HSSFSheet sheet = workbook.getSheetAt(i);
                // 4、获取sheet页中的行数
                int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();

                for (int j=0; j<physicalNumberOfRows; j++) {
                    if (j==0) {
                        continue; // 跳过标题行
                    }
                    // 6、获取行
                    HSSFRow row = sheet.getRow(j);
                    if (row == null) {
                        continue; // 防止数据中间有空行
                    }
                    // 7、获取列数
                    //int physicalNumberOfCells = row.getPhysicalNumberOfCells(); // 由于poi获取到如果是空白列不会认为是一列，所以会获取到的列数不准确，这里通过反射后去属性的个数作为总列数
                    Class<TestCase> clazz = TestCase.class;
                    int physicalNumberOfCells = clazz.getDeclaredFields().length;
                    testCase = new TestCase();
                    // 案例最后修改日期
                    testCase.setLastUpdateDate(DateUtil.date());
                    // 案例创建时间
                    testCase.setCreateDate(DateUtil.date());
                    testCase.setResultType(2);
                    testCase.setUserId(userId);
                    for (int k=0; k<=physicalNumberOfCells; k++) {
                        HSSFCell cell = row.getCell(k);
                        switch (k) {
                            case 1:
                                //设置单元格类型
                                cell.setCellType(CellType.STRING);
                                testCase.setRequest(cell.getStringCellValue());
                                break;
                            case 2:
                                //设置单元格类型
                                cell.setCellType(CellType.STRING);
                                testCase.setExpectedResponse(cell.getStringCellValue());
                                break;
                            case 5:
                                //设置单元格类型
                                cell.setCellType(CellType.STRING);
                                testCase.setBusiCode(cell.getStringCellValue());
                                break;
                            case 9:
                                //设置单元格类型
                                cell.setCellType(CellType.STRING);
                                testCase.setIgnoreFields(cell.getStringCellValue() == null? "" : cell.getStringCellValue());
                                break;
                            case 11:
                                //设置单元格类型
                                cell.setCellType(CellType.STRING);
                                testCase.setCaseName(cell.getStringCellValue() == null? "" : cell.getStringCellValue());
                                break;
                            case 12:
                                //设置单元格类型
                                cell.setCellType(CellType.STRING);
                                testCase.setCaseNameZh(cell.getStringCellValue() == null? "" : cell.getStringCellValue());
                                break;
                            default:
                                break;
                        }
                    }
                    list.add(testCase);
                }
            }
        } catch (Exception e) {
            logger.error("生成文件失败", e);
            throw new IOException();
        }

        return list;
    }

    public static void analyzeExcelGenerateSql() throws FileNotFoundException {

        String excelPath = "D:\\test\\base脚本生成.xlsx";
        File file = new File(excelPath);
        FileInputStream fileInputStream = new FileInputStream(file);
        List<String> sqlList = new LinkedList<>();
        try {
            // 1、创建一个workbook对象
            XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
            // 2、获取workboot中表单的数量
            int numberOfSheets = workbook.getNumberOfSheets();
            for (int i=0; i<numberOfSheets; i++) {
                // 3、获取sheet页
                XSSFSheet sheet = workbook.getSheetAt(i);
                // 4、获取sheet页中的行数
                int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();

                for (int j=0; j<physicalNumberOfRows; j++) {
                    if (j==0) {
                        continue; // 跳过标题行
                    }
                    // 6、获取行
                    XSSFRow row = sheet.getRow(j);
                    if (row == null) {
                        continue; // 防止数据中间有空行
                    }
                    // 7、获取列数
                    int physicalNumberOfCells = row.getPhysicalNumberOfCells(); // 由于poi获取到如果是空白列不会认为是一列，所以会获取到的列数不准确，这里通过反射后去属性的个数作为总列数
                    String methodName = "";     // 方法名
                    String aifgwBusiCode = "";  // 服务网关接口编号
                    String command = "";        // 备注信息
                    String imsBusiCode = "";    // 信管接口编号
                    String applicantName = "admin"; // 申请人姓名
                    for (int k=0; k<=physicalNumberOfCells; k++) {
                        XSSFCell cell = row.getCell(k);
                        switch (k) {
                            case 0:
                                //设置单元格类型
                                cell.setCellType(CellType.STRING);
                                methodName = cell.getStringCellValue();
                                break;
                            case 1:
                                //设置单元格类型
                                cell.setCellType(CellType.STRING);
                                aifgwBusiCode = cell.getStringCellValue();
                                break;
                            case 2:
                                //设置单元格类型
                                cell.setCellType(CellType.STRING);
                                command = cell.getStringCellValue();
                                break;
                            case 3:
                                //设置单元格类型
                                cell.setCellType(CellType.STRING);
                                imsBusiCode = cell.getStringCellValue() == null? "" : cell.getStringCellValue();
                                break;
                            case 4:
                                //设置单元格类型
                                cell.setCellType(CellType.STRING);
                                applicantName = cell.getStringCellValue() == null? "admin" : cell.getStringCellValue();
                                break;
                            default:
                                break;
                        }
                    }
                    sqlList.add("-- " + aifgwBusiCode + " " + imsBusiCode); // 注释行
                    String sql1 = "insert into base.net_srv_service (SERVICE_CODE, SERVICE_NAME, SERVICE_DESC, CHANNEL, PROCESS_CODE, MODIFIER, MODIFY_DATE, STATE, REMARK) values ('" + methodName +"', '" + command + "', '" + command + "', 'FUSE', '" + methodName +"', '" + applicantName +"', sysdate, 'U', '" + command + "');";
                    String sql2 = "insert into base.net_srv_process (PROCESS_CODE, PROCESS_NAME, PROCESS_DESC, PROCESS_ENGINE, EXTEND_PROPS, MODIFIER, MODIFY_DAE, STATE, REMARK) values ('" + methodName +"', '" + command + "', '" + command + "', 'DefaultErgodicEngine', null, '" + applicantName +"', sysdate, 'U', '" + command + "');";
                    String sql3 = "insert into base.net_srv_process_nodes (PROCESS_CODE, NODE_CODE, NODE_ORDER, CONDITIONS, EXTEND_PROPS, MODIFIER, MODIFY_DATE, STATE, REMARK) values ('" + methodName +"', 'FWZL_CALLER_NODE', 1, null, 'BusiCode=" + aifgwBusiCode + "', '" + applicantName +"', sysdate, 'U', null);";
                    sqlList.add(sql1);
                    sqlList.add(sql2);
                    sqlList.add(sql3);
                    sqlList.add(" ");
                }
            }

            // 将list内容写入到文件中
            listToFile(sqlList);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void listToFile(List<String> list) throws IOException {
        String filePath = "D:\\test\\testcz.sql";
        File file = new File(filePath);
        if (!file.exists()) {
            file.createNewFile();
        }

        FileWriter fileWriter = new FileWriter(file);

        for (String s : list) {
            fileWriter.write(s);
            fileWriter.write('\n');
        }

        fileWriter.close();
    }

    public static void main(String[] args) throws FileNotFoundException {
        //analyzeExcelGenerateSql();

        DataBaseParamter DataBaseParamter1 = new DataBaseParamter();
        DataBaseParamter DataBaseParamter2 = new DataBaseParamter();
        DataBaseParamter DataBaseParamter3 = new DataBaseParamter();

        DataBaseParamter1.setPassword("123");
        DataBaseParamter2.setPassword("123");
        DataBaseParamter3.setPassword("123");

        DataBaseParamter1.setIp("127.0.0.1");
        DataBaseParamter2.setIp("127.0.0.1");
        DataBaseParamter3.setIp("127.0.0.1");


        DataBaseParamter[] strings = new DataBaseParamter[] {DataBaseParamter1,DataBaseParamter2,DataBaseParamter3};
        Object o = JSONObject.toJSON(strings);
        //System.out.print(o.toString());
        Map map = new HashMap();
        map.put("rest", strings);

        String address = "曹杨五村242号,503\504室";
        //这个字符串，我想把 \ 替换成 \\
        //System.out.println(address.replaceAll("\\\\","&").replaceAll("&","\\\\\\\\"));
        System.out.println(address.replaceAll("\\\50","\\\\\\\\50"));
//        /System.out.print(new Gson().toJson(map));
    }
}
